package com.kaikeba.dao.Impl;

import com.kaikeba.bean.Courier;
import com.kaikeba.bean.User;
import com.kaikeba.dao.BaseCourierDao;
import com.kaikeba.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CourierDaoMySql implements BaseCourierDao {
    private static final String SQL_CONSOLE = "SELECT COUNT(ID) data_size, COUNT(TO_DAYS(REGTIME)=TO_DAYS(NOW()) OR NULL) data_day FROM ECOURIER";
    private static final String SQL_FIND_ALL = "SELECT * FROM ECOURIER";
    private static final String SQL_FIND_COURIER_PHONE = "SELECT COURIERPHONE FROM ECOURIER";
    private static final String SQL_FIND_ALL_LIMIT = "SELECT * FROM ECOURIER LIMIT ?,?";
    private static final String SQL_FIND_BY_COURIERPHONE = "SELECT * FROM ECOURIER WHERE COURIERPHONE=?";
    private static final String SQL_ADD = "INSERT ECOURIER(COURIERNAME,COURIERPHONE,NUMBER,PASSWORD,EXPRESSSUM,REGTIME,LASTLOGINTIME) VALUES(?,?,?,?,0,NOW(),NOW())";
    private static final String SQL_DELETE_BY_COURIERPHONE = "DELETE FROM ECOURIER WHERE COURIERPHONE=?";
    private static final String SQL_PUDATE_BY_ID = "UPDATE ECOURIER SET COURIERNAME=?,COURIERPHONE=?,NUMBER=?,PASSWORD=? WHERE ID=?";

    /**
     * 返回快递员总人数和日注册量
     *
     * @return
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            statement = conn.prepareStatement(SQL_CONSOLE);
            //填充参数（可选）
            //执行SQL语句
            resultSet = statement.executeQuery();
            //获取结果
            if(resultSet.next()){
                int data_size = resultSet.getInt("data_size");
                int data_day = resultSet.getInt("data_day");
                Map data1 = new HashMap();
                data1.put("data_size",data_size);
                data1.put("data_day",data_day);
                data.add(data1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }

    /**
     * 查询所有快递员号码
     *
     * @return
     */
    @Override
    public List<String> findCourierPhone() {
        ArrayList<String> data = new ArrayList<>();
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            statement = conn.prepareStatement(SQL_FIND_COURIER_PHONE);
            //填充参数（可选）
            //执行SQL语句
            resultSet = statement.executeQuery();
            //获取结果
            while (resultSet.next()){
                data.add(resultSet.getString("courierPhone"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }

    /**
     * 查询所有快递员的信息
     *
     * @return
     */
    @Override
    public List<Courier> findAll() {
        ArrayList<Courier> data = new ArrayList<>();
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            statement = conn.prepareStatement(SQL_FIND_ALL);
            //填充参数（可选）
            //执行SQL语句
            resultSet = statement.executeQuery();
            //获取结果
            while (resultSet.next()){
                Courier courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setCourierName(resultSet.getString("courierName"));
                courier.setCourierPhone(resultSet.getString("courierPhone"));
                courier.setNumber(resultSet.getString("number"));
                courier.setPassword(resultSet.getString("password"));
                courier.setExpressSum(resultSet.getInt("expressSum"));
                courier.setRegTime(resultSet.getTimestamp("regTime"));
                courier.setLastLoginTime(resultSet.getTimestamp("lastLoginTime"));
                data.add(courier);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }

    /**
     * 分页查询快递员的信息
     *
     * @param limit
     * @param offset
     * @param pageNumber
     * @return
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            if (limit){
                //预编译SQL语句
                statement = conn.prepareStatement(SQL_FIND_ALL_LIMIT);
                //添加参数（可选）
                statement.setInt(1,offset);
                statement.setInt(2,pageNumber);
            }else{
                //预编译SQL语句
                statement = conn.prepareStatement(SQL_FIND_ALL);
                //添加参数（可选）
            }
            //执行SQL语句
            resultSet = statement.executeQuery();
            //处理返回结果
            while (resultSet.next()){
                Courier courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setCourierName(resultSet.getString("courierName"));
                courier.setCourierPhone(resultSet.getString("courierPhone"));
                courier.setNumber(resultSet.getString("number"));
                courier.setPassword(resultSet.getString("password"));
                courier.setExpressSum(resultSet.getInt("expressSum"));
                courier.setRegTime(resultSet.getTimestamp("regTime"));
                courier.setLastLoginTime(resultSet.getTimestamp("lastLoginTime"));
                data.add(courier);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }

    /**
     * 通过快递员手机号码查询
     *
     * @param courierPhone
     * @return 快递员信息
     */
    @Override
    public Courier findByCourierPhone(String courierPhone) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_FIND_BY_COURIERPHONE);
            //添加参数（可选）
            statement.setString(1,courierPhone);
            //执行SQL语句
            resultSet = statement.executeQuery();
            //处理返回结果
            if (resultSet.next()){
                Courier courier = new Courier();
                courier.setId(resultSet.getInt("id"));
                courier.setCourierName(resultSet.getString("courierName"));
                courier.setCourierPhone(resultSet.getString("courierPhone"));
                courier.setNumber(resultSet.getString("number"));
                courier.setPassword(resultSet.getString("password"));
                courier.setExpressSum(resultSet.getInt("expressSum"));
                courier.setRegTime(resultSet.getTimestamp("regTime"));
                courier.setLastLoginTime(resultSet.getTimestamp("lastLoginTime"));
                return courier;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return null;
    }

    /**
     * 添加快递员
     *
     * @param courier 快递员
     * @return
     */
    @Override
    public boolean add(Courier courier) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_ADD);
            //添加参数（可选）
            statement.setString(1,courier.getCourierName());
            statement.setString(2,courier.getCourierPhone());
            statement.setString(3,courier.getNumber());
            statement.setString(4,courier.getPassword());
            //执行SQL语句
            int i = statement.executeUpdate();
            //处理返回结果
            return i>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement);
        }
        return false;
    }

    /**
     * 修改快递员信息
     *
     * @param newCourier 新的快递员信息
     * @return
     */
    @Override
    public boolean updateById(Courier newCourier) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_PUDATE_BY_ID);
            //添加参数（可选）
            statement.setString(1,newCourier.getCourierName());
            statement.setString(2,newCourier.getCourierPhone());
            statement.setString(3,newCourier.getNumber());
            statement.setString(4,newCourier.getPassword());
            statement.setInt(5,newCourier.getId());
            //执行SQL语句
            int i = statement.executeUpdate();
            //处理返回结果
            return i>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement);
        }
        return false;
    }

    /**
     * 根据快递员手机号删除快递员信息
     *
     * @param courierPhone
     * @return
     */
    @Override
    public boolean deleteByCourierPhone(String courierPhone) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_DELETE_BY_COURIERPHONE);
            //添加参数（可选）
            statement.setString(1,courierPhone);
            //执行SQL语句
            int i = statement.executeUpdate();
            //处理返回结果
            return i>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement);
        }
        return false;
    }
}
